package cn.edu.seu.cose.qol.model.dao.database.question;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.dao.database.util.StringArraySerializer;
import cn.edu.seu.cose.qol.model.po.question.Choice;
import cn.edu.seu.cose.qol.model.po.question.Questionnaire;


public class ChoiceDataAccess {
	
	private static final String COLUMN_CHOICE_ID = "question_choice_id";
	
	private static final String COLUMN_QUESTIONNAIRE_ID =
			"fk_questionnaire_id";
	
	private static final String COLUMN_QUESTION_TITLE = "question_title";
	
	private static final String COLUMN_OPITONS = "options";
	
	private static final String COLUMN_MAX_CHOICE_NUMBER =
			"max_choice_number";
	
	private static final String STATEMENT_RETRIEVE_CHOICE_BY_ID =
			"SELECT * FROM qol_question_choice WHERE question_choice_id=?;";
	
	private static final String STATEMENT_RETRIEVE_CHOICES =
			"SELECT * FROM qol_question_choice;";
	
	private static final String STATEMENT_RETRIEVE_CHOICES_BY_LIMIT =
			"SELECT * FROM qol_question_choice LIMIT ?, ?;";
	
	private static final String STATEMENT_RETRIEVE_CHOICES_RANDOMLY =
			"SELECT * FROM qol_question_choice ORDER BY RAND(NOW()) LIMIT ?;";
	
	private static final String STATEMENT_CREATE_CHOICE =
			"INSERT INTO qol_question_choice " +
			"(fk_questionnaire_id, question_title, " +
			"options, max_choice_number) VALUES " +
			"(?, ?, ?, ?);";
	
	private static final String STATEMENT_UPDATE_CHOICE =
			"UPDATE qol_question_choice SET " +
			"fk_questionnaire_id=?, " +
			"question_title=?, " +
			"options=?, " +
			"max_choice_number=? " +
			"WHERE question_choice_id=?;";
	
	private static final String STATEMENT_DELETE_CHOICE_BY_ID =
			"DELETE FROM qol_question_choice WHERE question_choice_id=?;";
	
	private static final String
			STATEMENT_DELETE_CHOICES_BY_QUESTIONNAIRE_ID =
				"DELETE FROM qol_question_choice WHERE fk_questionnaire_id=?;";
	
	private static ChoiceDataAccess _instance;
	
	private QolSchemaConnectionFactory _factory;
	
	private QuestionnaireDataAccess _questionnaireDataAccess;
	
	public static ChoiceDataAccess getInstance() {
		if (_instance == null) {
			_instance = new ChoiceDataAccess();
		}
		return _instance;
	}
	
	protected ChoiceDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
		_questionnaireDataAccess = QuestionnaireDataAccess.getInstance();
	}
	
	private Choice getChoiceByResultSet(ResultSet rs) throws SQLException {
		long choiceId = rs.getLong(COLUMN_CHOICE_ID);
		int questionnaireId = rs.getInt(COLUMN_QUESTIONNAIRE_ID);
		String questionTitle = rs.getString(COLUMN_QUESTION_TITLE);
		String optionsInString = rs.getString(COLUMN_OPITONS);
		int maxChoiceNumber = rs.getInt(COLUMN_MAX_CHOICE_NUMBER);
		
		StringArraySerializer serializer = StringArraySerializer.getInstance();
		Collection<String> options = serializer.deserialize(optionsInString);
		
		Questionnaire questionnaire =
				_questionnaireDataAccess.getQuestionnaireById(questionnaireId);
		Choice choice = new Choice();
		choice.setChoiceId(choiceId);
		choice.setMaxChoiceNumber(maxChoiceNumber);
		choice.setOptions(options);
		choice.setQuestionnnaire(questionnaire);
		choice.setQuestionTitle(questionTitle);
		
		return choice;
	}
	
	public Choice getChoiceById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_CHOICE_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			return getChoiceByResultSet(rs);
		} finally {
			connection.close();
		}
	}
	
	public Collection<Choice> getChoices() throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_CHOICES);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<Choice> choices = new LinkedList<Choice>();
			while (rs.next()) {
				Choice choice = getChoiceByResultSet(rs);
				choices.add(choice);
			}
			return choices;
		} finally {
			connection.close();
		}
	}
	
	public Collection<Choice> getChoices(long offset, long row)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_CHOICES_BY_LIMIT);
			ps.setLong(1, offset);
			ps.setLong(2, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<Choice> choices = new LinkedList<Choice>();
			while (rs.next()) {
				Choice choice = getChoiceByResultSet(rs);
				choices.add(choice);
			}
			return choices;
		} finally {
			connection.close();
		}
	}
	
	public Collection<Choice> getChoices(int row, boolean random)
			throws SQLException {
		if (!random) {
			return getChoices(0, row);
		}
		
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_CHOICES_RANDOMLY);
			ps.setLong(1, row);
			ResultSet rs = ps.executeQuery();
			
			LinkedList<Choice> choices = new LinkedList<Choice>();
			while (rs.next()) {
				Choice choice = getChoiceByResultSet(rs);
				choices.add(choice);
			}
			return choices;
		} finally {
			connection.close();
		}
	}
	
	public long createNewChoice(Choice choice) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_CHOICE, Statement.RETURN_GENERATED_KEYS);
			int questionnaireId = choice.getQuestionnnaire().getQuestionnaireId();
			String questionTitle = choice.getQuestionTitle();
			StringArraySerializer serializer = StringArraySerializer.getInstance();
			String options = serializer.serialize(choice.getOptions());
			int maxChoiceNumber = choice.getMaxChoiceNumber();
			ps.setInt(1, questionnaireId);
			ps.setString(2, questionTitle);
			ps.setString(3, options);
			ps.setInt(4, maxChoiceNumber);
			ps.executeUpdate();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (!rs.next()) {
				return -1;
			}
			return rs.getLong(1);
		} finally {
			connection.close();
		}
	}
	
	public void updateChoice(Choice choice) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_UPDATE_CHOICE);
			long choiceId = choice.getChoiceId();
			int questionnaireId = choice.getQuestionnnaire().getQuestionnaireId();
			String questionTitle = choice.getQuestionTitle();
			StringArraySerializer serializer = StringArraySerializer.getInstance();
			String options = serializer.serialize(choice.getOptions());
			int maxChoiceNumber = choice.getMaxChoiceNumber();
			
			ps.setInt(1, questionnaireId);
			ps.setString(2, questionTitle);
			ps.setString(3, options);
			ps.setInt(4, maxChoiceNumber);
			ps.setLong(5, choiceId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteChoiceById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_CHOICE_BY_ID);
			ps.setLong(1, id);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}
	
	public void deleteChoicesByQuestionnaireId(int questionnaireId)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_CHOICES_BY_QUESTIONNAIRE_ID);
			ps.setInt(1, questionnaireId);
			ps.executeUpdate();
		} finally {
			connection.close();
		}
	}

}
